/* 
 * Licensed Materials - Property of IBM
 * (c) Copyright IBM Corporation 2010. All Rights Reserved.
 *
 * Note to U.S. Government Users Restricted Rights:  Use,
 * duplication or disclosure restricted by GSA ADP Schedule
 * Contract with IBM Corp.
 *
 * This code is released under the terms of the Eclipse Public License
 * version 1.0 which can be found at the top level of this project or
 * or at http://www.eclipse.org/org/documents/epl-v10.php
 */

package com.ibm.mining;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

import com.ibm.mining.model.*;

public class PostgresResearchDatabase extends ResearchDatabase {

	public Connection connect(String jdbcConnectionString) throws SQLException {
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		}
		/*
		 * Here are possible formats
		 * jdbc:postgresql:database
		 * jdbc:postgresql://host/database
		 * jdbc:postgresql://host:port/database
		 * jdbc:postgresql://host:port/database?user=fred&password=secret&ssl=true
		 */
		conn = DriverManager.getConnection(jdbcConnectionString);
		return conn;
	}
	
	public String getDBTypeForType(Type type) {
		if (type.getName().equals("String")) {
			if (type.hasLength()) {
				return "varchar(" + type.getLength() + ")";
			}
			return "text";
		}
		if (type.getName().equals("Integer"))
			return "int";
		if (type.getName().equals("Timestamp"))
			return "timestamp";
		if (type.getName().equals("Boolean"))
			return "boolean";
		if (type.isModelClassType()) 
			return "varchar(100)";
		throw new RuntimeException("Can't determine database type for " + type.getName());
	}
	
	boolean tableExists(String tableName) throws SQLException {
		String sql = "select relname from pg_class where relname = '" + tableName + "';";
		ResultSet rs = conn.createStatement().executeQuery(sql);
		return rs.next();
		
	}
	
	public void dropTable(TableData td) throws SQLException {
		if (tableExists(td.name)) {
			String sql = "drop table " + td.name + ";";
			Logger.getLogger("com.ibm.mining").info(sql);
			conn.createStatement().execute(sql);
		}
	}
	
	public void createTable(TableData td, boolean onlyOutput) throws SQLException {
		String sql = "create table " + td.name + "(\n";
		List<String> cols = new LinkedList<String>();
		for (Pair<String, Type> column : td.columns) {
			cols.add( column.first + " " + getDBTypeForType(column.second));
		}
		if (td.hasKeys()) {
			cols.add("Primary Key(" + Util.join(td.keys, ", ") + ")");
		}
		sql += Util.join(cols, ",\n") + ");";
		
		Logger l = Logger.getLogger("com.ibm.mining");
		if (!onlyOutput) {
			/* only do it if the table doesn't already exist */
			Statement stmt = conn.createStatement();
			stmt.execute("select * from pg_tables where schemaname='public'" + 
					" and tablename = '" + td.name + "'");
			ResultSet rs = stmt.getResultSet();
			if (rs.next()) {
				l.info("It looks like table " + td.name + " already exists, skipping");
			} else {
				l.info("Creating table " + td.name);
				l.info(sql);
				conn.createStatement().execute(sql);
			}
			rs.close();
			stmt.close();
		}
	}
}
